package com.his.struts.dao.type;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.his.struts.dao.shebei.SheBeiImp;
import com.his.struts.form.TypeForm;
import com.his.struts.util.DbUtil;

/**
 * 疾病分类
 * 
 * @author 肖伟亮
 * 
 */
public class TypeImp implements TypeDao {
	Logger log = Logger.getLogger(TypeImp.class);
	DbUtil db = new DbUtil();

	/**
	 * 得到所有科室
	 * 
	 * @return 得到所有科室
	 */
	public List<TypeForm> getB_name(int kb_id) {
		ArrayList list = new ArrayList();
		String sql = "select * from bing where kb_id = ?";
		list.add(kb_id);
		ResultSet rs = db.executeQuery(sql, list);

		ArrayList questionList = new ArrayList();
		try {
			while (rs.next()) {
				TypeForm bing = new TypeForm();
				bing.setKb_id(rs.getInt("kb_id"));
				bing.setB_id(rs.getInt("b_id"));
				bing.setB_name(rs.getString("b_name"));
				questionList.add(bing);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}
		return questionList;
	}

	/**
	 * 得到该科室的所有科别
	 * 
	 * @param ks_id
	 *            科室id
	 * @return 得到该科室的所有科别
	 */
	public List<TypeForm> getKebie(int ks_id) {
		ArrayList list = new ArrayList();
		String sql = "select * from kebie where ks_id = ?";
		list.add(ks_id);
		ResultSet rs = db.executeQuery(sql, list);
		list.clear();
		ArrayList questionList = new ArrayList();
		try {
			while (rs.next()) {
				TypeForm kebie = new TypeForm();
				kebie.setKs_id(rs.getInt("ks_id"));
				kebie.setKb_id(rs.getInt("kb_id"));
				kebie.setKb_name(rs.getString("kb_name"));
				list.add(kebie);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}
		return list;
	}

	/**
	 * 得到该科别的所有病
	 * 
	 * @param kb_id
	 *            科别id
	 * @return 得到该科室的所有科别
	 */
	public List<TypeForm> getKeshi() {
		ArrayList list = new ArrayList();
		String sql = "select * from keshi";
		ResultSet rs = db.executeQuery(sql, null);
		try {
			while (rs.next()) {
				TypeForm keshi = new TypeForm();
				keshi.setKs_id(rs.getInt("ks_id"));
				keshi.setKs_name(rs.getString("ks_name"));
				list.add(keshi);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}
		return list;
	}

	/**
	 * 得到所有病
	 * 
	 * @return 所有病
	 */
	public List<TypeForm> getBean(ResultSet rs) {
		List<TypeForm> list = new ArrayList();
		try {
			while (rs.next()) {
				TypeForm type = new TypeForm();
				type.setKs_id(rs.getInt("ks_id"));
				type.setKb_id(rs.getInt("kb_id"));
				type.setB_id(rs.getInt("b_id"));
				type.setKs_name(rs.getString("ks_name"));
				type.setKb_name(rs.getString("kb_name"));
				type.setB_name(rs.getString("b_name"));
				type.setB_symptom(rs.getString("b_symptom"));
				list.add(type);

			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}

		return list;
	}

	/**
	 * 删除该病
	 * 
	 * @param bid
	 *            病id
	 */
	public void deleteDisease(int bid) {
		ArrayList list = new ArrayList();
		list.add(bid);
		/*
		 * 查出可别id
		 */
		String sql = "select kb_id from bing where b_id=?";
		ResultSet rs = db.executeQuery(sql, list);
		int kb_id = 0;
		try {
			if(rs.next()){
				kb_id = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeAll();
		}
		/*
		 * 删除疾病
		 */
		sql = "delete from bing where b_id=?";
		db.executeUpdate(sql, list);
		db.closeAll();
		
		/*
		 * 查出疾病中是否还有该科别
		 */
		list.clear();
		list.add(kb_id);
		sql = "select count(kb_id) from bing where kb_id=?";
		rs = db.executeQuery(sql, list);
		int n = 0;
		try {
			if(rs.next()){
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeAll();
		}
		// 无数据
		if(n==0){
			
		/*
		 * 查出该科别在科室id
		 */
		sql = "select ks_id from kebie where kb_id=?";
		rs = db.executeQuery(sql, list);
		int ks_id = 0;
		try {
			if(rs.next()){
				ks_id = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeAll();
		}
		/*
		 * 删除该科别
		 */
		sql = "delete from kebie where kb_id=?";
		db.executeUpdate(sql, list);
		db.closeAll();
		
		/*
		 * 查出科别中是否还有该科室
		 */
		list.clear();
		list.add(ks_id);
		sql = "select count(ks_id) from kebie where ks_id=?";
		rs = db.executeQuery(sql, list);
		n = 0;
		try {
			if(rs.next()){
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeAll();
		}
		if(n==0){
			/*
			 * 删除该科室
			 */
			sql = "delete from keshi where ks_id=?";
			db.executeUpdate(sql, list);
			db.closeAll();
		}
		
		}
		
	}

	/**
	 * 添加科室
	 * 
	 * @param ks_name
	 *            科室名称
	 * @return 该科室id
	 */
	public int addKeshi(String ks_name) {
		int n = 0;
		ArrayList list = new ArrayList();
		String sql = "insert into keshi(ks_name) values(?)";
		list.add(ks_name);
		db.executeUpdate(sql, list);
		sql = "select ks_id from keshi where ks_name=?";
		ResultSet rs = db.executeQuery(sql, list);
		try {
			if (rs.next()) {
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}
		return n;
	}

	/**
	 * 添加科别
	 * 
	 * @param ks_id
	 *            科室id
	 * @param kb_name
	 *            科别名称
	 * @return 该科别id
	 */
	public int addKebie(int ks_id, String kb_name) {
		int n = 0;
		ArrayList list = new ArrayList();
		String sql = "insert into kebie(ks_id,kb_name) values(?,?)";
		list.add(ks_id);
		list.add(kb_name);
		db.executeUpdate(sql, list);
		sql = "select kb_id from kebie where ks_id=? and kb_name=?";
		ResultSet rs = db.executeQuery(sql, list);
		try {
			if (rs.next()) {
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		db.closeAll();
		return n;
	}

	/**
	 * 添加疾病
	 * 
	 * @param kb_id
	 *            科别id
	 * @param b_name
	 *            疾病名称
	 */
	public void addJibing(int kb_id, String b_name,String b_symptom) {
		ArrayList list = new ArrayList();
		String sql = "insert into bing(kb_id,b_name,b_symptom) values(?,?,?)";
		list.add(kb_id);
		list.add(b_name);
		list.add(b_symptom);
		db.executeUpdate(sql, list);
		db.closeAll();
	}

	/**
	 * 得到科别名字
	 * 
	 * @param kb_id
	 *            科别id
	 * @return String 科别名
	 */
	public String getkb_name(int kb_id) {
		ArrayList list = new ArrayList();
		String sql = "select kb_name from kebie where kb_id =? ";
		list.add(kb_id);
		ResultSet rs = db.executeQuery(sql, list);
		String name = "";
		try {
			while (rs.next()) {
				name = rs.getString(1);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}
		return name;
	}

	/**
	 * 得到科别
	 * 
	 * @return 得到该科室的所有科别
	 */
	public List<TypeForm> getKebie() {
		ArrayList list = new ArrayList();
		String sql = "select * from kebie";
		ResultSet rs = db.executeQuery(sql, null);
		try {
			while (rs.next()) {
				TypeForm form = new TypeForm();
				int kb_id = rs.getInt("kb_id");
				String kb_name = rs.getString("kb_name");
				form.setKb_id(kb_id);
				form.setKb_name(kb_name);
				list.add(form);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}
		return list;
	}
	/**
	 * 得到科别名
	 * @param kb_id 科别id
	 * @return 
	 */
	public String getKeshiById(int ks_id) {
		ArrayList list = new ArrayList();
		String sql = "select kb_name from kebie where kb_id=?";
		list.add(ks_id);
		ResultSet rs = db.executeQuery(sql, list);
		String kebie = null;
		
		try {
			if(rs.next()){
				kebie = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeAll();
		}
		return kebie;
	}
	
	/**
	 * 封装疾病
	 * @param rs 疾病
	 * @return 疾病集合
	 */
	public List<TypeForm> getDiseaseBean(ResultSet rs) {
		
		List<TypeForm> list = new ArrayList();
		try {
			while (rs.next()) {
				TypeForm type = new TypeForm();
				type.setB_id(rs.getInt("b_id"));
				type.setB_name(rs.getString("b_name"));
				type.setB_symptom(rs.getString("b_symptom"));
				list.add(type);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			db.closeAll();
		}

		return list;
	}
	
	/**
	 * 更新疾病
	 * @param b_id 疾病id
	 * @param b_name 疾病名
	 * @param b_symptom 疾病症状
	 */
	public void updateDisease(int b_id,String b_name,String b_symptom) {
		String sql="update bing set b_name=?,b_symptom=? where b_id=?";
		ArrayList list = new ArrayList();
		list.add(b_name);
		list.add(b_symptom);
		list.add(b_id);
		db.executeUpdate(sql, list);
		db.closeAll();
	}
}
